using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class DepartmentsDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procDepartments_add]";
        private string _strSPUpdateName = "dbo.[procDepartments_update]";
        private string _strSPDeleteName = "dbo.[procDepartments_delete]";
        private string _strSPGetName = "dbo.[procDepartments_get]";
        private string _strSPGetAllName = "dbo.[procDepartments_getall]";
        private string _strSPGetPages = "dbo.[procDepartments_getpaged]";
        private string _strSPIsExist = "dbo.[procDepartments_isexist]";
        private string _strTableName = "[Departments]";
        private string _strSPGetTransferOutName = "dbo.[procDepartments_gettransferout]";
        string _strSPGetCountName = "procDepartments_getcount";
        string _strSPGetByIndexName = "procDepartments_getindex";
        #endregion Local Variable

        #region Method
        public DepartmentsInfo Get(
        String ROLE_ID,
        String DPT_ID,
        ref string sErr)
        {
            DepartmentsInfo objEntr = new DepartmentsInfo();
            InitConnect();
            InitSPCommand(@"SELECT     ROLE_ID, DPT_ID, Status
                FROM         Departments
                WHERE     (ROLE_ID = @ROLE_ID) AND (DPT_ID = @DPT_ID)");
            AddParameter(DepartmentsInfo.Field.ROLE_ID.ToString(), ROLE_ID);
            AddParameter(DepartmentsInfo.Field.DPT_ID.ToString(), DPT_ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new DepartmentsInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(ref string sErr, string ROLE_ID = null)
        {
            InitConnect();
            string sql = @"SELECT     ROLE_ID, DPT_ID, Status
                FROM         Departments ";
            if (ROLE_ID != null || ROLE_ID != "") sql += " WHERE  (ROLE_ID = @ROLE_ID) ";
            InitCommand(sql, CommandType.Text);
            if (ROLE_ID != null || ROLE_ID != "") AddParameter("ROLE_ID", ROLE_ID);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetByPos(
        int pos, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetByIndexName);
            AddParameter("INX", pos);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public int GetCountRecord(
        ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPGetCountName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(DepartmentsInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO Departments(ROLE_ID, DPT_ID, Status)
                VALUES     (@ROLE_ID,@DPT_ID,@Status)", CommandType.Text);
            AddParameter(DepartmentsInfo.Field.ROLE_ID.ToString(), objEntr.ROLE_ID);
            AddParameter(DepartmentsInfo.Field.DPT_ID.ToString(), objEntr.DPT_ID);
            AddParameter(DepartmentsInfo.Field.Status.ToString(), objEntr.Status);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(DepartmentsInfo objEntr)
        {
            openConnection();
            InitCommand(@"UPDATE    Departments
                    SET              Status = @Status
                WHERE     (ROLE_ID = @ROLE_ID) AND (DPT_ID = @DPT_ID)", CommandType.Text);
            AddParameter(DepartmentsInfo.Field.ROLE_ID.ToString(), objEntr.ROLE_ID);
            AddParameter(DepartmentsInfo.Field.DPT_ID.ToString(), objEntr.DPT_ID);
            AddParameter(DepartmentsInfo.Field.Status.ToString(), objEntr.Status);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(String ROLE_ID, String DPT_ID = null)
        {
            openConnection();
            string sql = @"DELETE FROM Departments
                WHERE     (ROLE_ID = @ROLE_ID)";
            if (DPT_ID != null) sql += " AND (DPT_ID = @DPT_ID) ";

            InitCommand(sql, CommandType.Text);
            AddParameter(DepartmentsInfo.Field.ROLE_ID.ToString(), ROLE_ID);
            if (DPT_ID != null) AddParameter(DepartmentsInfo.Field.DPT_ID.ToString(), DPT_ID);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

       

        public Boolean IsExist(
        String ROLE_ID,
        String DPT_ID
        )
        {
            InitConnect();
            InitCommand(@"SELECT     TOP (1) ROLE_ID, DPT_ID
                FROM         Departments
                WHERE     (ROLE_ID = @ROLE_ID) AND (DPT_ID = @DPT_ID)", CommandType.Text);
            AddParameter(DepartmentsInfo.Field.ROLE_ID.ToString(), ROLE_ID);
            AddParameter(DepartmentsInfo.Field.DPT_ID.ToString(), DPT_ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(DepartmentsInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            InitConnect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public DataTable getListInSub(string db, ref string sErr, string connectString = null)
        {
            string sql = @"SELECT RTRIM(CODE) as CODE
                FROM SSRFANV 
                WHERE CATEGORY = 'T4' AND PROHB_POST <> 'Y' AND SUN_DB = '" + db + "'";
            if (connectString == null || connectString == "") connectString = _connectionString;


            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlParameter p = command.CreateParameter(); p.ParameterName = "SUN_DB"; p.Value = db;
            command.Parameters.Add(p);

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();

            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex) { sErr = ex.Message; }
            return dt;
        }

        public DataTable getDpt(string role, ref string sErr)
        {
            throw new NotImplementedException();
        }
    }
}
